using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Configuration;

/// <summary>
/// Summary description for func
/// </summary>
public class func
{
    SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ketnoi2"].ToString());

	public func()
	{
	}
    public SqlConnection getConnection() {
        return conn;
    }
    ////FUNC GET DATAROWS
    public DataRow GETDATAROWS(string SQL)
    {
        try
        {
            SqlDataAdapter da = new SqlDataAdapter(SQL, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "TABLES");
            DataRow dr = ds.Tables["TABLES"].Rows[0];
            return dr;
        }
        catch (Exception ex) {
            return null;
        }
    }
    public DataSet GETDATASET(string SQL,string tenbang)
    {
        try
        {
            SqlDataAdapter da = new SqlDataAdapter(SQL, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, tenbang);
            return ds;
        }
        catch (Exception ex)
        {
            return null;
        }
    }

    //END FUNCTION GET DATAROWS
    ////FUNC EXECQUERY dung de thuc hien cau lenh insert update delete
    public int EXECQUERY(string SQL)
    {
        try
        {
            SqlCommand comm = new SqlCommand(SQL, conn);
            conn.Open();
            int i = comm.ExecuteNonQuery();
            return i;
        }
        catch (Exception ex) {
            return 0;
        }
    }
    //END FUNC EXECQUERY
    //function check exist 
    public bool CHECKEXIST(string sql)
    {
        try
        {
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            da.Fill(ds, "table");
            if (ds.Tables["table"].Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (Exception ex) {
            return false;
        }
    }
    //end function check exist





















    //START FUNC OF users////////////////////////////////////////////////////////////////////////////////////////////////
    public bool checkExistUser(string uid) {
        SqlDataAdapter da = new SqlDataAdapter("select * from users where UserID='"+uid+"'",conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "users");
        if (ds.Tables["users"].Rows.Count > 0)
        {
            return true;
        }
        else {
            return false;
        }
        
    }

    public bool checkExistUserByUserName(string uname)
    {
        SqlDataAdapter da = new SqlDataAdapter("select * from users where UserName='" + uname + "'", conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "users");
        if (ds.Tables["users"].Rows.Count > 0)
        {
            return true;
        }
        else
        {
            return false;
        }

    }

    public bool checkExistUserByNumberID(string numberID)
    {
        SqlDataAdapter da = new SqlDataAdapter("select * from users where NumberID='" + numberID + "'", conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "users");
        if (ds.Tables["users"].Rows.Count > 0)
        {
            return true;
        }
        else
        {
            return false;
        }

    }
    public DataRow getUsersInfoByID(string userID)
    {
        SqlDataAdapter da = new SqlDataAdapter("select * from users where UserID='" + userID + "'", conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "users");
        DataRow dr = ds.Tables["users"].Rows[0];
        return dr;

    }
    public DataRow getUsersInfoByUsername(string Username)
    {
        SqlDataAdapter da = new SqlDataAdapter("select * from users where UserName='" + Username + "'", conn);
        DataSet ds = new DataSet();
        da.Fill(ds, "users");
        DataRow dr = ds.Tables["users"].Rows[0];
        return dr;
    }
    public bool changePassword(string uid, string pass) {
        return false;
    }

    //END FUNC OF users////////////////////////////////////////////////////////////////////////////////////////////////
    //func of branch
    public bool BranchCheckExistByID(string id)
    {
        string sql = "select * from branches where BranchID='" + id + "'";
        return CHECKEXIST(sql);
    }
    public bool BranchCreate(string name,string location,string phone) {
        string sql = @"INSERT INTO Branches values('"+name+"','"+location+"','"+phone+"',getdate(),1)";
        if (EXECQUERY(sql) > 0)
        {
            return true;
        }
        else {
            return false;
        }
    }
    public bool BranchUpdateInfo(string id,string name,string location,string phone)
    {
        try
        {
            string sql = "update branches set BranchName='" + name + "',Location='" + location + "',Phone='" + phone + "' where BranchID='" + id + "';";
            int i = EXECQUERY(sql);
            if (i > 0)
            {
                return true;
            }
            else {
                return false;
            }
        }
        catch (Exception ex) {
            return false;
        }
    }
    

    //end func of branch
    ////////START ROUTES
    public bool RoutesCheckExistByID(string id)
    {
        string sql = "select * from Routes where RouteID='" + id + "'";
        return CHECKEXIST(sql);
    }
    //END ROUTES


}